Excel BI - Excel Challenge 848

excel-challenges

excel-formulas

🔰 Split the data into individual alphabets.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 848

Challenge Description

🔰 Split the data into individual alphabets.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/800-899/848/848 Alignment.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "B1:AF10") %>%
  mutate(across(everything(), ~ str_replace_all(replace_na(., ""), "\\.", "")))

align_matrix <- function(words) {
  chars <- str_split(words, "")
  accumulate(
    chars,
    \(a, c) {
      x <- intersect(c, a)
      if (!length(x)) return(c)
      s <- which(a == x[1])[1] - which(c == x[1])[1]
      if (s < 0) return(c)
      c(rep("", s), c)
    }
  ) |>
    {\(al) {
      m <- max(lengths(al))
      do.call(rbind, map(al, \(x) c(x, rep("", m - length(x)))))
    }}()
}

all.equal(
  align_matrix(input[[1]]) %>% as_tibble(),
  test,
  check.attributes = FALSE,
  check.names = FALSE
)

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd
import numpy as np

path = "Excel/800-899/848/848 Alignment.xlsx"

input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B:AF", nrows=10)
test = test.fillna("").astype(str).replace(r"\.", "", regex=True)

def align_matrix(words):
    aligned = [list(words[0])]
    for c in map(list, words[1:]):
        a = aligned[-1]
        common = set(c) & set(a)
        if not common:
            aligned.append(c)
            continue
        ch = next(ch for ch in c if ch in a)
        s = a.index(ch) - c.index(ch)
        aligned.append([""] * s + c if s > 0 else c)
    m = max(len(x) for x in aligned)
    return np.array([x + [""] * (m - len(x)) for x in aligned])

aligned_array = align_matrix(input.iloc[:, 0])
aligned_df = pd.DataFrame(aligned_array)
aligned_df.columns = test.columns

print(aligned_df.equals(test)) # True

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.